import excel using "Ratio Data 1978-1988.xlsx", ///
	firstrow

*Residential ratio data has letters in some of the values
*Delete these strings out of the ratio data
*Preserve them as a separate variable 

gen tmp1=ResidentialMed

gen resid_ratio_code="S" if regexm(tmp1, "S")
replace resid_ratio_code="A" if regexm(tmp1, "A")

replace tmp1=subinstr(tmp1, "S","",.)
replace tmp1=subinstr(tmp1, "A","",.)

* Rename and convert to numeric
gen resid_median = real(tmp1)
drop tmp1

*Confirm no missing data is due to strings in the ratio
tab ResidentialMed if resid_median==.

drop ResidentialMedian
rename ResidentialCOD resid_COD
rename Sales resid_sales
rename County county
rename Year year
tempfile ratio_1978_1988
save `ratio_1978_1988'

// Import and save each sheet in Ratio Data for 1989, 1990, 1993, 1996.xlsx
* n/a's are treated as missing
* Create starting dataset with 1989
import excel using "Ratio Data for 1989, 1990, 1993, 1996.xlsx", ///
	sheet(1989) firstrow clear
gen int year = 1989
gen resid_median = real(Residential)
gen resid_sales = real(C)
gen resid_COD = real(D)
gen farm_median = real(Farm)
gen farm_sales = real(G)
gen farm_COD = real(H)
gen commerc_median = real(Commercial)
gen commerc_sales = real(K)
gen commerc_COD = real(L)
rename County county
drop if _n == 1
drop if missing(county)
keep county year-commerc_COD
save "assessment_sales_ratio_1989.dta", replace
tempfile ratio_1993 
save `ratio_1993'
tempfile ratio_data
save `ratio_data'

*---
* 1993 and 1996 data

foreach num of numlist 1993 1996{
	import excel using "Ratio Data for 1989, 1990, 1993, 1996.xlsx", ///
		sheet(`num') firstrow clear
	gen int year = `num'
	gen resid_median = real(Residential)
	gen resid_sales = real(D)
	gen resid_COD = real(C)
	gen farm_median = real(Farm)
	gen farm_sales = real(H)
	gen farm_COD = real(G)
	gen commerc_median = real(Commercial)
	gen commerc_sales = real(L)
	gen commerc_COD = real(K) if K != "n/a"
	rename County county
	drop if _n == 1
	keep county year-commerc_COD
	save "assessment_sales_ratio_`num'.dta", replace
	tempfile ratio_`num'
	save `ratio_`num''
	
	// append data
	use `ratio_data', clear
	append using `ratio_`num''
	drop if missing(county)
	save `ratio_data', replace
}

*---
* 1992, 1994, 1995, and 1998 data


foreach num of numlist 1992 1994 1995 1998{
	import excel using "Ratio Data for 1992, 1994, 1995, 1998.xlsx", ///
		sheet(`num') firstrow clear
	gen int year = `num'
	gen resid_median = real(Residential)
	gen resid_sales = real(D)
	gen resid_COD = real(C)
	rename County county
	drop if _n == 1
	keep county year-resid_COD
	save "assessment_sales_ratio_`num'.dta", replace
	tempfile ratio_`num'
	save `ratio_`num''
	
	// append data
	use `ratio_data', clear
	append using `ratio_`num''
	drop if missing(county)
	save `ratio_data', replace
}


* 1990 data
import excel using "Ratio Data for 1989, 1990, 1993, 1996.xlsx", ///
	sheet(1990) firstrow clear
gen int year = 1990
gen resid_median = real(Residential)
gen resid_sales = real(H)
gen resid_COD = real(G)
gen farm_median = real(Farm)
gen farm_sales = real(D)
gen farm_COD = real(C)
gen commerc_median = real(Commercial)
gen commerc_sales = real(L)
gen commerc_COD = real(K)
rename County county
drop if _n == 1
keep county year-commerc_COD
save "assessment_sales_ratio_1990.dta", replace
tempfile ratio_1990
save `ratio_1990'

// append datasets
use `ratio_data', clear
append using `ratio_1990'
append using `ratio_1978_1988'
replace county = "Larue" if county == "LaRue"
replace county= "Woodford" if county=="Woodfrod"
drop if missing(county)
sort county year
save `ratio_data', replace
drop if year == 1978

//Merge in FIPS code
merge m:1 county using ky_countyname_fips.dta
drop _merge

order fipsco county year resid_median resid_COD

local eraseyears "1989 1990 1998"

foreach y of local eraseyears {
	erase assessment_sales_ratio_`y'.dta
}

forvalues y=1992/1996 {

erase assessment_sales_ratio_`y'.dta

}

replace resid_median = 101 if county=="Jackson" & year==1984

gen countyfip = "21"+fipsco
destring countyfip, force replace

*---
* Save DTA
save "assessment_sales_ratio_data.dta", replace
clear
